﻿IF EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'usp_UPDMS_COM_Get_New_Id_By_Table')
    BEGIN
        DROP  Procedure usp_UPDMS_COM_Get_New_Id_By_Table
    END
GO

SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
GO
/******************************************************************************
**	Name : usp_UPDMS_COM_Get_New_Id_By_Table
**	Desc : 테이블별로 관리되는 신규아이디 발번
**	Test Exec Query : exec usp_UPDMS_COM_Get_New_Id_By_Table 'UPDMS_REF_SHOP', 'Shop_Id', 'SP', '3'
**	Called by : 전체
**	Program ID : 전체
**	Auth : 송시명
**	Date : 2013-04-11
*******************************************************************************
**	Change History
*******************************************************************************
**	Date:		Author:		Description: 
**	--------	--------	--------------------------------------- 
**	
*******************************************************************************/ 
CREATE PROC [dbo].[usp_UPDMS_COM_Get_New_Id_By_Table]
@ls_table varchar(100), --테이블명
@ls_column varchar(100), --컬럼명
@ls_prefix nvarchar(10), --접두어
@ls_digit char(1) --자릿수(1~9)
AS
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
SET NOCOUNT ON

DECLARE @sql nvarchar(max)

SET @sql = '
DECLARE @init_id VARCHAR(20) --초기아이디(데이터가 없을때)
DECLARE @prefix_len INT --접두어의 길이
SET @prefix_len = LEN(''' + @ls_prefix + ''')

SELECT @init_id = ''' + @ls_prefix + ''' + REPLACE(STR(''0000000000'', ' + @ls_digit + '), '' '', ''0'')

SELECT LEFT(Id, @prefix_len) + REPLACE(STR(RIGHT(Id, ' + @ls_digit + ') + 1, ' + @ls_digit + '),'' '', ''0'') AS New_Id
  FROM
     (
       SELECT TOP 1 Id
         FROM
            (
              SELECT @init_id AS Id

              UNION ALL

              SELECT ' + @ls_column + '
                FROM ' + @ls_table + '
            ) A
        ORDER BY Id DESC
     ) rslt
'

EXEC (@sql)

GO
